"""
默认模版
@author: 任磊
@software: Pycharm
@time: 2024/5/13 21:29
"""

import os
import sys
import pymysql
import socket
from PyQt5 import QtCore, QtWidgets
from PyQt5.QtGui import QIcon
from PyQt5.QtWidgets import QApplication, QMainWindow, QTableWidgetItem, QAbstractItemView, QMessageBox
import json
import pysm4
import random
import threading
import time

class Sever_window(object):

    def __init__(self, main_window):
        super().__init__()
        self.main_window = main_window

    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(800, 600)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.horizontalLayoutWidget_2 = QtWidgets.QWidget(self.centralwidget)
        self.horizontalLayoutWidget_2.setGeometry(QtCore.QRect(9, 10, 781, 51))
        self.horizontalLayoutWidget_2.setObjectName("horizontalLayoutWidget_2")
        self.horizontalLayout_2 = QtWidgets.QHBoxLayout(self.horizontalLayoutWidget_2)
        self.horizontalLayout_2.setContentsMargins(0, 0, 0, 0)
        self.horizontalLayout_2.setObjectName("horizontalLayout_2")
        spacerItem = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)
        self.horizontalLayout_2.addItem(spacerItem)
        self.label = QtWidgets.QLabel(self.horizontalLayoutWidget_2)
        self.label.setObjectName("label")
        self.horizontalLayout_2.addWidget(self.label)
        spacerItem1 = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)
        self.horizontalLayout_2.addItem(spacerItem1)
        self.commandLinkButton = QtWidgets.QCommandLinkButton(self.horizontalLayoutWidget_2)
        self.commandLinkButton.setObjectName("commandLinkButton")
        self.horizontalLayout_2.addWidget(self.commandLinkButton)
        self.verticalLayoutWidget_2 = QtWidgets.QWidget(self.centralwidget)
        self.verticalLayoutWidget_2.setGeometry(QtCore.QRect(10, 70, 241, 481))
        self.verticalLayoutWidget_2.setObjectName("verticalLayoutWidget_2")
        self.verticalLayout_2 = QtWidgets.QVBoxLayout(self.verticalLayoutWidget_2)
        self.verticalLayout_2.setContentsMargins(0, 0, 0, 0)
        self.verticalLayout_2.setObjectName("verticalLayout_2")
        self.label_2 = QtWidgets.QLabel(self.verticalLayoutWidget_2)
        self.label_2.setObjectName("label_2")
        self.verticalLayout_2.addWidget(self.label_2)
        self.tableWidget = QtWidgets.QTableWidget(self.verticalLayoutWidget_2)
        self.tableWidget.setObjectName("tableWidget")
        self.tableWidget.setColumnCount(2)
        self.tableWidget.setRowCount(0)
        item = QtWidgets.QTableWidgetItem()
        self.tableWidget.setHorizontalHeaderItem(0, item)
        item = QtWidgets.QTableWidgetItem()
        self.tableWidget.setHorizontalHeaderItem(1, item)
        self.verticalLayout_2.addWidget(self.tableWidget)
        self.verticalLayoutWidget_3 = QtWidgets.QWidget(self.centralwidget)
        self.verticalLayoutWidget_3.setGeometry(QtCore.QRect(540, 70, 251, 481))
        self.verticalLayoutWidget_3.setObjectName("verticalLayoutWidget_3")
        self.verticalLayout_3 = QtWidgets.QVBoxLayout(self.verticalLayoutWidget_3)
        self.verticalLayout_3.setContentsMargins(0, 0, 0, 0)
        self.verticalLayout_3.setObjectName("verticalLayout_3")
        self.label_3 = QtWidgets.QLabel(self.verticalLayoutWidget_3)
        self.label_3.setObjectName("label_3")
        self.verticalLayout_3.addWidget(self.label_3)
        self.tableWidget_2 = QtWidgets.QTableWidget(self.verticalLayoutWidget_3)
        self.tableWidget_2.setObjectName("tableWidget_2")
        self.tableWidget_2.setColumnCount(2)
        self.tableWidget_2.setRowCount(0)
        item = QtWidgets.QTableWidgetItem()
        self.tableWidget_2.setHorizontalHeaderItem(0, item)
        item = QtWidgets.QTableWidgetItem()
        self.tableWidget_2.setHorizontalHeaderItem(1, item)
        self.verticalLayout_3.addWidget(self.tableWidget_2)
        # 用户类型
        self.label_type = QtWidgets.QLabel(self.centralwidget)  # 账户类型标签
        self.label_type.setGeometry(QtCore.QRect(260, 270, 70, 30))
        self.label_type.setObjectName("label_type")
        self.comboBox = QtWidgets.QComboBox(self.centralwidget)
        self.comboBox.setGeometry(QtCore.QRect(340, 270, 120, 30))
        self.comboBox.setObjectName("comboBox")
        self.comboBox.addItem("")
        self.comboBox.addItem("")
        self.comboBox.addItem("")
        # 用户数据输入
        self.label_4 = QtWidgets.QLabel(self.centralwidget)  # 账户名标签
        self.label_4.setGeometry(QtCore.QRect(260, 310, 70, 30))
        self.label_4.setObjectName("label_4")
        self.lineEdit = QtWidgets.QLineEdit(self.centralwidget)  # 账户名输入栏
        self.lineEdit.setGeometry(QtCore.QRect(340, 309, 180, 30))
        self.lineEdit.setObjectName("lineEdit")
        self.label_5 = QtWidgets.QLabel(self.centralwidget)  # 账户密码输入标签
        self.label_5.setGeometry(QtCore.QRect(260, 350, 70, 30))
        self.label_5.setObjectName("label_5")
        self.lineEdit_2 = QtWidgets.QLineEdit(self.centralwidget)  # 账户密码输入栏
        self.lineEdit_2.setGeometry(QtCore.QRect(340, 350, 180, 30))
        self.lineEdit_2.setObjectName("lineEdit_2")
        self.pushButton = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton.setGeometry(QtCore.QRect(270, 400, 75, 30))
        self.pushButton.setObjectName("pushButton")
        self.pushButton_2 = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton_2.setGeometry(QtCore.QRect(440, 400, 75, 30))
        self.pushButton_2.setObjectName("pushButton_2")
        self.label_6 = QtWidgets.QLabel(self.centralwidget)
        self.label_6.setGeometry(QtCore.QRect(260, 440, 300, 30))
        self.label_6.setObjectName("label_6")
        self.textEdit = QtWidgets.QTextEdit(self.centralwidget)
        self.textEdit.setGeometry(QtCore.QRect(260, 70, 271, 190))
        self.textEdit.setObjectName("textEdit")
        self.lineEdit_3 = QtWidgets.QLineEdit(self.centralwidget)
        self.lineEdit_3.setGeometry(QtCore.QRect(260, 500, 161, 30))
        self.lineEdit_3.setObjectName("lineEdit_3")
        self.pushButton_3 = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton_3.setGeometry(QtCore.QRect(440, 500, 75, 30))
        self.pushButton_3.setObjectName("pushButton_3")
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtWidgets.QMenuBar(MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 800, 30))
        self.menubar.setObjectName("menubar")
        MainWindow.setMenuBar(self.menubar)
        self.statusbar = QtWidgets.QStatusBar(MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)

        self.retranslateUi(MainWindow)
        QtCore.QMetaObject.connectSlotsByName(MainWindow)

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "选课管理系统服务端"))
        self.label.setText(_translate("MainWindow", "选课管理系统服务端"))
        self.commandLinkButton.setText(_translate("MainWindow", "安全退出"))
        self.commandLinkButton.clicked.connect(self.exit_button)
        self.label_2.setText(_translate("MainWindow", "用户状态列表"))
        item = self.tableWidget.horizontalHeaderItem(0)
        item.setText(_translate("MainWindow", "用户名"))
        item = self.tableWidget.horizontalHeaderItem(1)
        item.setText(_translate("MainWindow", "用户状态"))
        self.label_3.setText(_translate("MainWindow", "用户请求列表"))
        item = self.tableWidget_2.horizontalHeaderItem(0)
        item.setText(_translate("MainWindow", "用户名"))
        item = self.tableWidget_2.horizontalHeaderItem(1)
        item.setText(_translate("MainWindow", "用户请求"))
        self.label_type.setText(_translate("MainWindow", "类型:"))
        self.label_4.setText(_translate("MainWindow", "账户名:"))
        self.lineEdit.setPlaceholderText("输入用户名")
        self.label_5.setText(_translate("MainWindow", "密码:"))
        self.lineEdit_2.setPlaceholderText("输入或修改密码")
        self.pushButton.setText(_translate("MainWindow", "修改"))
        self.pushButton.clicked.connect(self.alter_client_pwd_function)
        self.pushButton_2.setText(_translate("MainWindow", "添加"))
        self.pushButton_2.clicked.connect(self.create_client_function)
        self.label_6.setText(_translate("MainWindow", "等待操作..."))
        self.lineEdit_3.setPlaceholderText("输入MySql语句")
        self.pushButton_3.setText(_translate("MainWindow", "执行"))
        self.pushButton_3.clicked.connect(self.top_query_function)
        self.comboBox.setItemText(0, _translate("MainWindow", "教务处"))
        self.comboBox.setItemText(1, _translate("MainWindow", "学院"))
        self.comboBox.setItemText(2, _translate("MainWindow", "学生"))
        # 绑定信号
        # Signal_program.update_client_state.connect(self.update_client_state_list_show)

    def update_client_state_list_show(self):
        """
        用于更新用户状态列表的方法
        :return: None
        """
        # 数据清空
        self.tableWidget.setRowCount(0)
        # 插入数据
        self.tableWidget.setRowCount(len(Main_program.client_name_list))
        self.tableWidget.setColumnCount(2)
        counter = 0
        for i, data in enumerate(Main_program.client_name_list):
            self.tableWidget.setItem(counter, 0, QTableWidgetItem(data))
            self.tableWidget.setItem(counter, 1, QTableWidgetItem(Main_program.client_state_list[i]))
            counter += 1
        self.tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)

    def update_client_request_list_show(self):
        """
        用于更新用户请求列表的方法
        :return: None
        """
        # 数据清空
        self.tableWidget_2.setRowCount(0)
        # 插入数据
        self.tableWidget_2.setRowCount(len(Main_program.client_request_name_list))
        self.tableWidget_2.setColumnCount(2)
        counter = 0
        for i, data in enumerate(Main_program.client_request_name_list):
            self.tableWidget_2.setItem(counter, 0, QTableWidgetItem(data))
            self.tableWidget_2.setItem(counter, 1, QTableWidgetItem(Main_program.client_request_data_list[i]))
            counter += 1
        self.tableWidget_2.setEditTriggers(QAbstractItemView.NoEditTriggers)

    def alter_client_pwd_function(self):
        """
        用于实现修改指定类型和用户密码的方法，并屏幕上显示操作结果
        :return: None
        """
        self.pushButton.clicked.disconnect()
        client_name = self.lineEdit.text()
        client_new_pwd = self.lineEdit_2.text()
        client_type = self.comboBox.currentText()
        self.label_6.setText("")
        if client_name == "":
            self.label_6.setText("用户名不可为空！")
        elif client_new_pwd == "":
            self.label_6.setText("密码不可为空！")
        else:
            data = Mysql_program.query_data(f"select * from user_table where name = '{client_name}' and type = '{client_type}';")
            if data == ():
                self.label_6.setText("不存在该用户！")
            else:
                if Mysql_program.insert_or_update_data(f"update user_table set pwd_hash = '{client_new_pwd}' where name = '{client_name}' and type = '{client_type}';") is True and Main_program.update_client_data_list(client_type, client_name, client_new_pwd) is True:
                    self.lineEdit.setText("")
                    self.lineEdit_2.setText("")
                    self.label_6.setText("修改成功！")
                else:
                    self.label_6.setText("修改失败！")
        self.pushButton.clicked.connect(self.alter_client_pwd_function)

    def create_client_function(self):
        """
        用于实现添加指定类型用户的方法，在屏幕上显示操作结果
        :return: None
        """
        self.pushButton_2.clicked.disconnect()
        client_type = self.comboBox.currentText()
        client_name = self.lineEdit.text()
        client_pwd = self.lineEdit_2.text()
        self.label_6.setText("")
        if client_name == "":
            self.label_6.setText("用户名不可为空！")
        elif client_pwd == "":
            self.label_6.setText("密码不可为空!")
        else:
            data = Mysql_program.query_data(f"select * from user_table where type = '{client_type}' and name = '{client_name}';")
            if data == ():
                if Mysql_program.insert_or_update_data(f"insert into user_table(type, name, pwd_hash) values('{client_type}', '{client_name}', '{client_pwd}');") is True:
                    self.label.setText("用户添加成功！")
                else:
                    self.label_6.setText("用户添加失败！")
            else:
                self.label_6.setText("该用户已存在！")
        self.pushButton_2.clicked.connect(self.create_client_function)

    def top_query_function(self):
        """
        该方法为实现自由查询的方法,并在屏幕上显示响应结果
        :return: None
        """
        self.pushButton_3.clicked.disconnect()
        query_data = self.lineEdit_3.text()
        self.label_6.setText("")
        if len(query_data) <= 6:
            self.label_6.setText("输入似乎无效！")
        elif "select" == query_data[0:6] or "insert into" == query_data[:11] or "update" == query_data[:6] or "delete" == query_data[:6]:
            if query_data[len(query_data)-1:len(query_data)] != ";":  # 自动添加结尾标志
                query_data += ';'
            self.lineEdit_3.setText("")
            self.label_6.setText("已执行")
            mode = 0
            if "select" not in query_data:
                mode = 1
            Sever_window.show_top_query_function(query_data, mode)
        else:
            self.label_6.setText("语法似乎无效！")
        self.pushButton_3.clicked.connect(self.top_query_function)

    @staticmethod
    def show_top_query_function(query_data, mode):
        flag = True
        if mode == 0:
            data = ()
            try:
                data = Mysql_program.query_data(query_data)
            except pymysql.err.ProgrammingError:
                flag = False
            finally:
                if flag is True:
                    if data == ():
                        QMessageBox.information(None, "NOTE", "未查询到数据！")
                    else:
                        show_data = ""
                        for item in data:
                            show_data += str(item) + "\n"
                        QMessageBox.information(None, "NOTE", show_data)
                else:
                    QMessageBox.critical(None, 'ERROR', '执行错误！', QMessageBox.Yes)
        else:
            data = False
            try:
                data = Mysql_program.insert_or_update_data(query_data)
            except pymysql.err.ProgrammingError:
                flag = False
            finally:
                if flag is True:
                    if data is True:
                        QMessageBox.information(None, "NOTE", "执行成功！")
                    else:
                        QMessageBox.warning(None, 'NOTE', '执行失败！', QMessageBox.Yes)
                else:
                    QMessageBox.critical(None, 'ERROR', '执行错误！', QMessageBox.Yes)

    def exit_button(self):
        """
        退出幅度按系统的方法，会根据当前是否已连接客户端，若有则提示尚有用户连接无法退出，反之则退出系统
        :return: NOne
        """
        if Connect_program.now_listen == 0:
            Main_program.work_flag = False
            time.sleep(0.1)
            exit()
        else:
            self.label_6.setText("")
            self.label_6.setText("尚有用户通信中，无法关闭！")


class Main_program(object):
    """
    系统主线程的类
    """
    client_name_list = []  # 用户名列表
    client_pwd_list = []  # 用户密码列表
    client_type_list = []  # 用户类型列表
    client_state_list = []  # 用户状态列表
    # 系统参数
    work_flag = False  # 服务器工作状态标志
    update_client_state_flag = False  # 用户状态信息列表更新标志
    client_request_old_counter = 0  # 用户请求计数
    client_request_now_counter = 0
    client_request_counter_max = 10  # 请求列表最大存储请求数量
    client_request_name_list = []
    client_request_data_list = []
    # 根窗口
    main_window = ''

    def __init__(self, main_window):
        super().__init__()
        self.window = main_window
        Main_program.main_window = main_window
        self.window.setWindowIcon(QIcon('校徽1.png'))
        self.init_ui()

    def init_ui(self):
        """
        系统数据准备，并启用服务端监听线程的方法
        :return: None
        """
        if Mysql_program.get_user_all_data() is True:  # 判断用户数据读取结果
            self.login_window = Sever_window(self.window)
            self.login_window.setupUi(self.window)
            self.login_window.update_client_state_list_show()
            # 改变工作状态标志
            Main_program.work_flag = True
            # 创建并开启用户状态列表更新线程
            th1 = threading.Thread(target=Main_program.show_client_state_process, args=(self,))
            th1.start()
            # 创建并开启用户请求列表更新线程
            th2 = threading.Thread(target=Main_program.show_client_request_process, args=(self, ))
            th2.start()
            # 创建并开启通信监听线程
            th3 = threading.Thread(target=Connect_program.listen_start)
            th3.setDaemon(True)  # 设置为守护线程
            th3.start()  # 开启服务端监听
        else:
            QMessageBox.information(self.window, "错误", "读取用户数据异常！")

    def show_client_state_process(self):
        """
        实现用户状态列表更新的线程方法
        :return: None
        """
        while Main_program.work_flag is True:
            if Main_program.update_client_state_flag is True:
                Main_program.update_client_state_flag = False
                Sever_window.update_client_state_list_show(self.login_window)

    @staticmethod
    def show_client_request_process(self):
        """
        实现用户请求列表更新的线程方法
        :return: None
        """
        while Main_program.work_flag is True:
            time.sleep(0.1)
            Main_program.client_request_now_counter = len(Main_program.client_request_name_list)
            if Main_program.client_request_now_counter == Main_program.client_request_old_counter:
                Main_program.client_request_old_counter += 1
                if Main_program.client_request_now_counter > Main_program.client_request_counter_max:
                    Main_program.client_request_name_list.remove(Main_program.client_request_name_list[0])
                    Main_program.client_request_data_list.remove(Main_program.client_request_data_list[0])
                Sever_window.update_client_request_list_show(self.login_window)

    @staticmethod
    def update_client_state_list(stype, name, state):
        """
        用于修改用户状态列表的方法
        :param stype: 用户类型
        :param name: 用户名
        :param state: 用户状态标志，1 在线，0 离线
        :return: None
        """
        for i, n in enumerate(Main_program.client_name_list):
            if n == name and Main_program.client_type_list[i] == stype:
                if str(state) == '1':
                    Main_program.client_state_list[i] = "在线"
                else:
                    Main_program.client_state_list[i] = "离线"
                break

    @staticmethod
    def update_client_data_list(stype, name, pwd):
        """
        用于更新用户密码信息列表的方法
        :param stype: 用户类型
        :param name: 用户名
        :param pwd: 用户新密码
        :return: True/False
        """
        flag = False
        for i, n in enumerate(Main_program.client_name_list):
            if n == name and Main_program.client_type_list[i] == stype:
                Main_program.client_pwd_list[i] = pwd
                flag = True
                break
        return flag


class Client_program(object):
    """
    用户在服务端的存在方式的类
    """
    def __init__(self, index, sock, flag):
        super().__init__()
        self.name = Main_program.client_name_list[int(index)]  # 获得用户名
        self.pwd = Main_program.client_pwd_list[int(index)]  # 获得用户密码
        self.type = Main_program.client_type_list[int(index)]  # 获得用户类型
        self.sock = sock  # 获得用户通信体
        self.connect_flag = flag

    def client_mid_start(self):
        """
        判断用户类型并启用对应用户的方法
        :return: None
        """
        if self.type == "教务处":
            Main_program.update_client_state_list(self.type, self.name, 1)
            Main_program.update_client_state_flag = True
            self.jwu_process()
        elif self.type == "学院":
            Main_program.update_client_state_list(self.type, self.name, 1)
            Main_program.update_client_state_flag = True
            self.xy_process()
        else:
            Main_program.update_client_state_list(self.type, self.name, 1)
            Main_program.update_client_state_flag = True
            self.student_process()

    def jwu_process(self):
        """
        用于处理教务处用户的线程方法，其中功能包括：
        课程录入：录入课程信息，向客户端返回处理结果
        退学处理：首先在退学学生信息表中查询目标学号以判断该学号是否已经进行了退学处理，
                判断在学生信息表中是否存在目标学生学生号，存在则进行处理，返回退学处理结果
        :return:None
        """
        while True:
            data = self.sock.recv(1024).decode()
            Main_program.client_request_name_list.append(self.name)
            Main_program.client_request_data_list.append(data)
            if data == "jwc_course_table_insert":  # 课程录入
                data = self.sock.recv(1024).decode()
                data = json.loads(data)
                course_cid = data['course_cid']
                course_name = data['course_name']
                course_classroom = data['course_classroom']
                course_teacher = data['course_teacher']
                course_date = data['course_date']
                course_pri_cid = data['course_pre_cid']
                course_credit = data['course_credit']
                course_note = data['course_note']
                course_done = data['course_done']
                # 判断数据插入结果
                if Mysql_program.insert_or_update_data(f"insert into course_table(cid, name, classroom, teacher, date, pre_cid, credit, note, done_date) values ('{course_cid}', '{course_name}', '{course_classroom}', '{course_teacher}', '{course_date}', '{course_pri_cid}', '{course_credit}', '{course_note}', '{course_done}');") is True:
                    # 返回插入结果
                    self.sock.send("TRUE".encode())
                else:
                    self.sock.send("FALSE".encode())
            elif data == "jwc_student_table_delete":  # 退学处理
                data = self.sock.recv(1024).decode()
                data = json.loads(data)
                student_sid = data["student_sid"]
                data = Mysql_program.query_data(f"select * from student_table where sid = '{student_sid}';")
                # 判断学生信息表中是否存在该学号
                if data == ():
                    # 判断是否在退学学生表中找到该学号
                    data = Mysql_program.query_data(f"select * from drop_out_student_table where sid = '{student_sid}';")
                    if data != ():
                        # 已经处理过
                        self.sock.send("HAVE_DONE".encode())
                    else:
                        # 无此学号
                        self.sock.send("NONE".encode())
                else:
                    student_sid = data[0]['sid']
                    student_name = data[0]['name']
                    student_sex = data[0]['gender']
                    student_age = data[0]['age']
                    student_address = data[0]['address']
                    student_exit_date = str(time.ctime())
                    # 判断是否移除了学生信息(包括学生信息表，选课信息表,用户信息表)
                    if Mysql_program.insert_or_update_data(f"delete from student_table where sid = '{student_sid}';") is True and Mysql_program.insert_or_update_data(f"delete from score_table where sid = '{student_sid}';") is True and Main_program.update_client_data_list(f"delete from user_table where type = '{self.type}' and name = '{student_name}';"):
                        if Mysql_program.insert_or_update_data(f"insert into drop_out_student_table(sid, name, gender, age, address, drop_out_date) values ('{student_sid}', '{student_name}', '{student_sex}', '{student_age}', '{student_address}', '{student_exit_date}');"):
                            self.sock.send("OK".encode())
                        else:
                            self.sock.send("ERROR".encode())
                    else:
                        self.sock.send("FALSE".encode())
            elif data == "jwc_score_table_delete":  # 退课处理
                data = self.sock.recv(1024).decode()
                data = json.loads(data)
                student_sid = data["student_sid"]
                course_cid = data["course_cid"]
                # 判断学生是否申请退该课程
                data = Mysql_program.query_data(f"select delapp from score_table where sid = '{student_sid}' and cid = '{course_cid}';")
                if data == ():
                    en_data = Mysql_program.query_data(f"select * from score_table where sid = '{student_sid}' and cid = '{course_cid}';")
                    if en_data == ():
                        data = "null"
                    else:
                        data = 'have'
                else:
                    data = data[0]['delapp']
                if data == '0':
                    self.sock.send("NONE_THIS".encode())
                elif data == "null":
                    self.sock.send("NONE_SID".encode())
                else:
                    # 判断删除该学号选课是否成功
                    if Mysql_program.insert_or_update_data(f"delete from score_table where sid = '{student_sid}' and cid = '{course_cid}';") is True:
                        self.sock.send("OK".encode())
                    else:
                        self.sock.send("FALSE".encode())
            elif data == "jwc_sus_update":  # 休复学处理
                 data = self.sock.recv(1024).decode()
                 data = json.loads(data)
                 student_sid = data["student_sid"]
                 mode = data["mode"]
                 if mode == "suspend":
                     data_list = Mysql_program.query_data(f"select sus from student_table where sid = '{student_sid}';")
                     # 判断该学生是否已经进行了休学操作
                     if str(data_list[0]['sus']) == '1':
                         self.sock.send("HAVE_DONE".encode())
                     else:
                         if Mysql_program.insert_or_update_data(f"update student_table set sus = TRUE where sid = '{student_sid}';") is True:
                             self.sock.send("OK".encode())
                         else:
                             self.sock.send("FALSE".encode())
                 else:
                     data = Mysql_program.query_data(f"select * from student_table where sid = '{student_sid}';")
                     if str(data[0]['sus']) == "0":
                         self.sock.send("HAVE_DONE".encode())
                     else:
                         if Mysql_program.insert_or_update_data(f"update student_table set sus = FALSE where sid = '{student_sid}';") is True:
                             self.sock.send("OK".encode())
                         else:
                             self.sock.send("FALSE".encode())
            elif data == "jwc_reg_update":  # 注册处理
                data = self.sock.recv(1024).decode()
                data = json.loads(data)
                student_sid = data['student_sid']
                data = Mysql_program.query_data(f"select reg from student_table where sid='{student_sid}';")
                # 判断该学号状况
                if data == ():
                    self.sock.send("NONE".encode())
                else:
                    if str(data[0]["reg"]) == "1":
                        self.sock.send("HAVE_DONE".encode())
                    else:
                        if Mysql_program.insert_or_update_data(f"update student_table set reg = TRUE where sid = '{student_sid}';") is True:
                            self.sock.send("OK".encode())
                        else:
                            self.sock.send("FALSE".encode())
            elif data == "jwc_pwd_update":  # 密码修改
                data = self.sock.recv(1024).decode()
                data = json.loads(data)
                old_pwd = data["old_pwd"]
                new_pwd = data["new_pwd"]
                if self.pwd == old_pwd:
                    if Mysql_program.insert_or_update_data(f"update user_table set pwd_hash = '{new_pwd}' where name = '{self.name}' and type = '{self.type}';") is True and Main_program.update_client_data_list(self.type, self.name, new_pwd) is True:
                        self.sock.send("OK".encode())
                    else:
                        self.sock.send("FALSE".encode())
                else:
                    self.sock.send("ERROR".encode())
            else:  # 退出
                self.sock.close()
                Main_program.update_client_data_list(self.type, self.name, 0)
                break
        Main_program.update_client_state_list(self.type, self.name, 0)
        Main_program.update_client_state_flag = True
        Connect_program.now_listen -= 1

    def xy_process(self):
        """
        实现学院用户进程的方法
        :return: None
        """
        while True:
            data = self.sock.recv(1024).decode()
            Main_program.client_request_name_list.append(self.name)
            Main_program.client_request_data_list.append(data)
            if data == "xy_student_table_insert":  # 学生信息录入
                data = self.sock.recv(1024).decode()
                data = json.loads(data)
                student_sid = data["student_sid"]
                student_name = data["student_name"]
                student_gender = data["student_gender"]
                student_age = int(data["student_age"])
                student_address = data["student_address"]
                data = Mysql_program.query_data(f"select * from student_table where sid = '{student_sid}';")
                if data == ():
                    if Mysql_program.insert_or_update_data(f"insert into student_table(sid, name, gender, age, address, sus, reg) values ('{student_sid}', '{student_name}', '{student_gender}', {student_age}, '{student_address}', FALSE, FALSE);") is True:
                       self.sock.send("OK".encode())
                    else:
                        self.sock.send("FALSE".encode())
                else:
                    self.sock.send("HAVE_DONE".encode())
            elif data == "xy_score_table_select":  # 选课查询
                data_list = Mysql_program.query_data(f"select sid, cid, score, delapp from score_table;")
                num_data = len(data_list)
                self.sock.send(str(num_data).encode())
                if num_data != 0:
                    time.sleep(0.1)
                    for data in data_list:
                        data = json.dumps(data)
                        self.sock.send(data.encode())
                        time.sleep(0.1)
            elif data == "xy_course_table_select":  # 课程信息查询
                data_list = Mysql_program.query_data(f"select cid, name, classroom, teacher, date, pre_cid, credit, note, done_date from course_table;")
                num_data = len(data_list)
                self.sock.send(str(num_data).encode())
                if num_data != 0:
                    time.sleep(0.1)
                    for data in data_list:
                        data = json.dumps(data)
                        self.sock.send(data.encode())
                        time.sleep(0.1)
            elif data == "xy_student_table_select":  # 学生信息查询
                data_list = Mysql_program.query_data(f"select sid, name, gender, age, address, sus, reg from student_table;")
                num_data = len(data_list)
                self.sock.send(str(num_data).encode())
                if num_data != 0:
                    time.sleep(0.1)
                    for data in data_list:
                        data = json.dumps(data)
                        self.sock.send(data.encode())
                        time.sleep(0.1)
            elif data == "xy_score_table_update":  # 成绩录入
                data = self.sock.recv(1024).decode()
                data = json.loads(data)
                student_sid = data["student_sid"]
                course_cid = data["course_cid"]
                course_score = data["course_score"]
                if Mysql_program.query_data(f"select sid from student_table where sid = '{student_sid}';") == ():
                    self.sock.send("NONE_SID".encode())
                elif Mysql_program.query_data(f"select cid from course_table where cid ='{course_cid}';") == ():
                    self.sock.send("NONE_CID".encode())
                elif Mysql_program.query_data(f"select * from score_table where sid = '{student_sid}' and cid = '{course_cid}';") == ():
                    self.sock.send("NONE_THIS".encode())
                else:
                    data = Mysql_program.query_data(f"select score from score_table where sid = '{student_sid}' and cid = '{course_cid}';")
                    if str(data[0]['score']) == "None":
                        if Mysql_program.insert_or_update_data(f"update score_table set score = '{course_score}' where sid = '{student_sid}' and cid = '{course_cid}';"):
                            self.sock.send("OK".encode())
                        else:
                            self.sock.send("FALSE".encode())
                    else:
                        self.sock.send("HAVE_DONE".encode())
            elif data == "xy_pwd_update":  # 密码修改
                data = self.sock.recv(1024).decode()
                data = json.loads(data)
                new_pwd = data["new_pwd"]
                if Mysql_program.insert_or_update_data(f"update user_table set pwd_hash ='{new_pwd}' where type = '{self.type}' and name = '{self.name}';") is True and Main_program.update_client_data_list(self.type, self.name, new_pwd) is True:
                    self.sock.send("OK".encode())
                else:
                    self.sock.send("FALSE".encode())
            else:  # 退出
                self.sock.close()
                Main_program.update_client_data_list(self.type, self.name, 0)
                break
        Main_program.update_client_state_list(self.type, self.name, 0)
        Main_program.update_client_state_flag = True
        Connect_program.now_listen -= 1

    def student_process(self):
        """
        实现学生用户的方法
        :return:
        """
        while True:
            data = self.sock.recv(1024).decode()
            Main_program.client_request_name_list.append(self.name)
            Main_program.client_request_data_list.append(data)
            if data == "stu_score_table_insert":  # 选课
                data = self.sock.recv(1024).decode()
                data = json.loads(data)
                course_cid = data["course_cid"]
                # 判断学生是否已注册
                data = Mysql_program.query_data(f"select reg from student_table where sid = '{self.name}';")
                if str(data[0]['reg']) == "1":
                    if Mysql_program.query_data(f"select * from course_table where cid = '{course_cid}';") == ():
                        self.sock.send("NONE_CID".encode())
                    elif Mysql_program.query_data(f"select * from score_table where cid = '{course_cid}' and sid = '{self.name}'") != ():
                        self.sock.send("HAVE_DONE".encode())
                    else:
                        # 判断选课时间是否冲突
                        flag_1 = True
                        dict_list = []
                        data_list = Mysql_program.query_data(f"select * from score_table where sid = '{self.name}';")
                        if data_list != ():
                            # 获得学生已选课的时间列表数据
                            course_date_list = Mysql_program.query_data(f"select date from course_table where cid in (select cid from score_table where sid = '{self.name}');")
                            # 获得目标选课的时间数据
                            choose_course_date = Mysql_program.query_data(f"select date from course_table where cid = '{course_cid}';")
                            # 数据格式修正
                            choose_course_date = Mysql_program.repair_to_dict(choose_course_date[0]['date'])
                            choose_course_date = json.loads(choose_course_date)
                            choose_day1 = ''
                            choose_day2 = ''
                            for choose_day1, choose_day2 in choose_course_date.items():
                                pass
                            for data in course_date_list:
                                dict_list.append(json.loads(Mysql_program.repair_to_dict(data['date'])))
                            for data in dict_list:
                                for k, v in data.items():
                                    if k == choose_day1:
                                        if v in choose_day2 or choose_day2 in v:
                                            flag_1 = False
                                if flag_1 is False:
                                    break
                        # 判断选课学分是否已达到18分
                        credit_counter = 0
                        data_list = Mysql_program.query_data(f"select credit from course_table where cid in (select cid from score_table where sid = '{self.name}');")
                        for data in data_list:
                            credit_counter += int(data['credit'])
                        target_credit = int(Mysql_program.query_data(f"select credit from course_table where cid = '{course_cid}';")[0]['credit'])
                        # 判断是否缺少先修课程
                        flag_2 = True
                        pre_cid = Mysql_program.query_data(f"select pre_cid from course_table where cid = '{course_cid}';")[0]['pre_cid']
                        if pre_cid != "000":
                            data = Mysql_program.query_data(f"select * from score_table where cid = '{pre_cid}' and sid = '{self.name}';")
                            if data == ():
                                flag_2 = False
                        if flag_1 is True:
                            if credit_counter + target_credit <= 18:  # 判断已选课程学分与即将选课学分之和是否大于 18
                                if flag_2 is True:
                                    # 判断是否成功选课
                                    if Mysql_program.insert_or_update_data(f"insert into score_table(sid, cid ) values ('{self.name}', '{course_cid}');") is True:
                                        self.sock.send("OK".encode())
                                    else:
                                        self.sock.send("FALSE".encode())
                                else:
                                    self.sock.send("NONE_PRECID".encode())
                            else:
                                self.sock.send("HAVE_FULL_CREDITS".encode())
                        else:
                            self.sock.send("TIME_ERROR".encode())
                else:
                    self.sock.send("NO_REGISTER".encode())
            elif data == "stu_score_table_update":  # 选课查询
                data_list = Mysql_program.query_data(f"select sid, cid, score, delapp from score_table where sid = '{self.name}';")
                num_data = str(len(data_list))
                self.sock.send(num_data.encode())
                if num_data != 0:
                    time.sleep(0.1)
                    for data in data_list:
                        data = json.dumps(data)
                        self.sock.send(data.encode())
                        time.sleep(0.1)
            elif data == "stu_score_table_delete":  # 退课请求
                data = self.sock.recv(1024).decode()
                data = json.loads(data)
                course_cid = data['course_cid']
                if Mysql_program.query_data(f"select cid from score_table where cid = '{course_cid}' and sid = '{self.name}';") == ():
                    self.sock.send("NONE_CID".encode())
                elif Mysql_program.compare_course_date(course_cid) is False:
                    data = Mysql_program.query_data(f"select score from score_table where sid = '{self.name}' and cid = '{course_cid}';")[0]['score']
                    if str(data) == "None":
                        if Mysql_program.insert_or_update_data(f"update score_table set delapp = TRUE where cid='{course_cid}' and sid='{self.name}';") is True:
                            self.sock.send("APPLY_DELETE_TRUE".encode())
                        else:
                            self.sock.send("APPLY_DELETE_FALSE".encode())
                    else:
                        self.sock.send("DENY_DELETE".encode())
                else:
                    if Mysql_program.insert_or_update_data(f"delete from score_table where cid = '{course_cid}' and sid = '{self.name}';") is True:
                        self.sock.send("OK".encode())
                    else:
                        self.sock.send("FALSE".encode())
            elif data == "stu_pwd_update":  # 修改密码
                data = self.sock.recv(1024).decode()
                data = json.loads(data)
                new_pwd = data["new_pwd"]
                if Mysql_program.insert_or_update_data(f"update user_table set pwd_hash = '{new_pwd}' where name = '{self.name}' and type = '{self.type}';") is True and Main_program.update_client_data_list(self.type, self.name, new_pwd) is True:
                    self.sock.send("OK".encode())
                else:
                    self.sock.send("FALSE".encode())
            else:  # 退出
                self.sock.close()
                Main_program.update_client_data_list(self.type, self.name, 0)
                break
        Main_program.update_client_state_list(self.type, self.name, 0)
        Main_program.update_client_state_flag = True
        Connect_program.now_listen -= 1


class Connect_program(object):
    """
    用于实现服务端与客户端进行通信的类
    """
    max_listen = 5  # 最大监听人数
    now_listen = 0  # 实时监听人数

    def __init__(self):
        super().__init__()

    @staticmethod
    def listen_start():
        """
        用于启动服务端监听的方法，将该方法作为线程使用，同时应该设置为守护线程，保证同时结束系统运行
        :return: None
        """
        sever = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        sever.bind(('0.0.0.0', 8000))
        sever.listen()
        while Main_program.work_flag is True:
            while Connect_program.now_listen < Connect_program.max_listen:
                sock, address = sever.accept()
                # 创建并启用用户线程
                th = threading.Thread(target=Connect_program.mid_connect, args=(sock,))
                th.start()

    @staticmethod
    def mid_connect(sock):
        """
        用于中间验证用户身份的方法，若通过验证则通过该方法为用户创建用户线程
        此处的验证机制为：当服务端接收到正确的用户名与用户类型后，会随机生成数字，使用用户的密码加密后发往客户端，客户端需使用密码解密后，
                      在原数字的基础上加1，再将结果发往服务端，服务端验证后，将验证结果发往客户端
        :param sock: 通信体
        :return: None
        """
        Connect_program.now_listen += 1
        index = None
        flag = False
        find_flag = False
        # 接收用户类型
        client_type = sock.recv(1024).decode()
        # 接收用户名
        client_name = sock.recv(1024).decode()
        for index, name in enumerate(Main_program.client_name_list):
            if name == client_name and Main_program.client_type_list[index] == client_type:
                find_flag = True
                break
        if find_flag is True:
            sock.send("TRUE".encode())
            # 生成并发送验证数字
            num = str(random.randint(100, 5000))
            en_num = pysm4.encrypt_ecb(num, Main_program.client_pwd_list[index])
            sock.send(en_num.encode())
            # 接收结果
            re_num = sock.recv(1024).decode()
            num = str(int(num) + 1)
            # 验证并发送结果
            if re_num != 'ERROR':
                if re_num == num:
                    sock.send("TRUE".encode())
                    flag = True
                else:
                    sock.send("FALSE".encode())
        else:
            sock.send("FALSE".encode())
        if flag is True:  # 判断验证结果，启用用户线程
            client = Client_program(index, sock, True)
            th = threading.Thread(target=client.client_mid_start)
            th.start()
        else:
            Connect_program.now_listen -= 1
            sock.close()


class Mysql_program(object):
    """
    用可以实现节点与数据库进行连接，以及完成数据库的基本操作
    """
    @staticmethod
    def get_conn():
        """
        获取MySQL链接
        :return: mysql connection
        """
        return pymysql.connect(
            host='localhost',  # 指定连接的主机类型
            user='root',  # 用户类型
            password='3014007467',
            database="sjkdzy",
            charset='utf8'
        )

    @staticmethod
    def query_data(sql):
        """
        根据SQL查询数据并且返回
        :param sql:SQL语句
        :return:list[dict]
        """
        conn = Mysql_program.get_conn()
        try:
            cursor = conn.cursor(pymysql.cursors.DictCursor)
            cursor.execute(sql)
            return cursor.fetchall()
        finally:
            conn.close()  # 关闭连接

    @staticmethod
    def insert_or_update_data(sql):
        """
        执行新增insert或者update的sql,返回执行结果
        :param sql: insert or update sql
        :return: True or flag
        """
        flag = False
        conn = Mysql_program.get_conn()
        try:
            cursor = conn.cursor()
            cursor.execute(sql)
            conn.commit()  # 注意这里只有commit才可以生效
            flag = True
        finally:
            conn.close()  # 关闭连接
            return flag

    @staticmethod
    def get_user_all_data():
        """
        用于获取用户所有数据，并添加数据至指定列表的方法，返回处理结果
        :return: flag
        """
        flag = False
        data_list = Mysql_program.query_data(f"select * from user_table;")
        if data_list == ():
            flag = True
        else:
            try:
                for data in data_list:
                    Main_program.client_name_list.append(data['name'])
                    Main_program.client_pwd_list.append(data['pwd_hash'])
                    Main_program.client_type_list.append(data['type'])
                    Main_program.client_state_list.append("离线")
                flag = True
            except KeyError:
                flag = False
            finally:
                pass
        return flag

    @staticmethod
    def repair_to_dict(test_str):
        """
        用于修正mysql中读取的时间字符串格式异常问题，返回修正后的字符串
        :param test_str: 待修正字符串
        :return: 修正后的字符串
        """
        end_str = ""
        for i in test_str:
            if i == "u":
                end_str += "\\" + i
            else:
                end_str += i
        return end_str

    @staticmethod
    def compare_course_date(cid):
        """
        用于实现判断选课是否已开课，返回判断结果
        :param cid: 课程号
        :return: True or flag
        """
        course_date = Mysql_program.query_data(f"select done_date from course_table where cid = '{cid}';")[0]['done_date']
        if course_date == ():
            return True
        else:
            now_date = Mysql_program.query_data("select now();")[0]['now()']
            if str(now_date) > course_date:
                return False
            else:
                return True

    @staticmethod
    def get_num(date_str):
        """
        从目标字符串获得数字的方法，返回数字字符串
        :param date_str: 目标字符串
        :return: 数字字符串
        """
        end_str = ''
        for data in date_str:
            if data.isdigit():
                end_str += data
        return end_str


if __name__ == "__main__":
    os.environ['QT_QPA_PLATFORM_PLUGIN_PATH'] = "E:\pycharm_major\数据库专题大作业\.venv\Lib\site-packages\PyQt5\Qt5\plugins"
    app = QApplication(sys.argv)

    window1 = QMainWindow()

    program = Main_program(window1)

    window1.show()

    sys.exit(app.exec_())
